﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WaveMaster.Holidays.Club.Gateway;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections;
using WaveMaster.Holidays.Club.Domain;
using ThoughtWorks.QRCode.Codec;
using System.IO;
using System.Drawing;
using CrystalDecisions.CrystalReports.Engine;
using System.Configuration;

namespace WaveMaster.Holidays.Club.Service
{
    public class ReportRequestService : System.MarshalByRefObject
    {
        DataBaseConnectivity m_dataBaseConectivity;
        CheckInService m_checkInService;

        public ReportRequestService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_checkInService = new CheckInService();
        }

        //public IList GetBoardingPassRequestReportByIPAddress(string ipaddress)
        //{
        //    IList result = new ArrayList();
        //    m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //    MySqlCommand cmd = new MySqlCommand("select * from report_request r where r.ip_address=?ip_address and r.report_type = 'BP' and r.status = 'Open'", m_dataBaseConectivity.InterfaceSqlConnection);
        //    cmd.Parameters.Add("ip_address", MySqlDbType.VarChar).Value = ipaddress;
        //    MySqlDataReader rd = cmd.ExecuteReader();
        //    while (rd.Read())
        //    {
        //        ReportRequest rrs = new ReportRequest(rd);
        //        result.Add(rrs);
        //    }
        //    rd.Close();
        //    IList datareslt = new ArrayList();
        //    foreach (ReportRequest rr in result)
        //    {
        //        DataSet bp = m_checkInService.ReportItineraryBookingPassengerByID(rr.report_id);
        //        bp.Tables[0].Columns.Add("barcodeImage", typeof(byte[]));
        //        ArrayList l = new ArrayList();
        //        int i = bp.Tables[0].Rows[0].ItemArray.Length;
        //        int j = 0;
        //        foreach (object o in bp.Tables[0].Rows[0].ItemArray)
        //        {
        //            if (j == (i - 1))
        //            {
        //                l.Add(getBarcode2DImage(l[23].ToString()));// index 23 is boarding pass code.
        //            }
        //            else
        //                l.Add(o);
        //            j++;
        //        }
        //        bp.Tables[0].Rows[0].ItemArray = l.ToArray();
        //        //bp.Tables[0].Columns.Add(rr.m_printerNameBP);// add printer name......... numpang

        //        datareslt.Add(bp);

        //        cmd.Parameters.Clear();
        //        cmd.CommandText = "update report_request set status = 'Close' where id=?id";
        //        cmd.Parameters.Add("id", MySqlDbType.Int32).Value = rr.id;
        //        cmd.ExecuteNonQuery();
        //    }
        //    m_dataBaseConectivity.InterfaceSqlConnection.Close();

        //    return datareslt;
        //}

        //public IList GetWhiteCardRequestReportByIPAddress(string ipaddress)
        //{
        //    IList result = new ArrayList();
        //    m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //    MySqlCommand cmd = new MySqlCommand("select * from report_request r where r.ip_address=?ip_address and r.report_type = 'WC' and r.status = 'Open'", m_dataBaseConectivity.InterfaceSqlConnection);
        //    cmd.Parameters.Add("ip_address", MySqlDbType.VarChar).Value = ipaddress;
        //    MySqlDataReader rd = cmd.ExecuteReader();
        //    while (rd.Read())
        //    {
        //        ReportRequest rrs = new ReportRequest(rd);
        //        result.Add(rrs);
        //    }
        //    rd.Close();
        //    IList datareslt = new ArrayList();
        //    foreach (ReportRequest rr in result)
        //    {
        //        DataSet bp = m_checkInService.ReportItineraryBookingPassengerByID(rr.report_id);
        //        bp.Tables[0].Columns.Add(rr.m_printerNameWC);
        //        datareslt.Add(bp);

        //        cmd.Parameters.Clear();
        //        cmd.CommandText = "update report_request set status = 'Close' where id=?id";
        //        cmd.Parameters.Add("id", MySqlDbType.Int32).Value = rr.id;
        //        cmd.ExecuteNonQuery();
        //    }
        //    m_dataBaseConectivity.InterfaceSqlConnection.Close();

        //    return datareslt;
        //}
        public IList GetBoardingPassRequestReportByMACAddress(string macAddress)
        {
            IList result = new ArrayList();
            IList datareslt = new ArrayList();
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand("select * from report_request r where r.m_macAddress=?m_macAddress and r.report_type = 'BP' and r.status = 'Open'", m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = macAddress;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    ReportRequest rrs = new ReportRequest(rd);
                    result.Add(rrs);
                }
                rd.Close();
                
                foreach (ReportRequest rr in result)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = "update report_request set status = 'Close' where id=?id";
                    cmd.Parameters.Add("id", MySqlDbType.Int32).Value = rr.id;
                    cmd.ExecuteNonQuery();

                    DataSet bp = m_checkInService.ReportItineraryBookingPassengerByID(rr.report_id);
                    bp.Tables[0].Columns.Add("barcodeImage", typeof(byte[]));
                    ArrayList l = new ArrayList();
                    int i = bp.Tables[0].Rows[0].ItemArray.Length;
                    int j = 0;
                    foreach (object o in bp.Tables[0].Rows[0].ItemArray)
                    {
                        if (j == (i - 1))
                        {
                            l.Add(getBarcode2DImage(l[23].ToString()));// index 23 is boarding pass code.
                        }
                        else
                            l.Add(o);
                        j++;
                    }
                    bp.Tables[0].Rows[0].ItemArray = l.ToArray();
                    bp.Tables[0].Columns.Add(rr.m_printerNameBP);// add printer name......... numpang
                    datareslt.Add(bp);
                }
                return datareslt;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }

        }

        public IList GetBoardingPassRequestTwoWayReportByMACAddress(string macAddress)
        {
            IList result = new ArrayList();
            IList datareslt = new ArrayList();
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand("select * from report_request r where r.m_macAddress=?m_macAddress and r.report_type = 'BP2' and r.status = 'Open'", m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = macAddress;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    ReportRequest rrs = new ReportRequest(rd);
                    result.Add(rrs);
                }
                rd.Close();
                foreach (ReportRequest rr in result)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = "update report_request set status = 'Close' where id=?id";
                    cmd.Parameters.Add("id", MySqlDbType.Int32).Value = rr.id;
                    cmd.ExecuteNonQuery();

                    DataSet bp = m_checkInService.ReportItineraryBookingPassengerByID(rr.report_id);
                    bp.Tables[0].Columns.Add("barcodeImage", typeof(byte[]));
                    ArrayList l = new ArrayList();
                    int i = bp.Tables[0].Rows[0].ItemArray.Length;
                    int j = 0;
                    foreach (object o in bp.Tables[0].Rows[0].ItemArray)
                    {
                        if (j == (i - 1))
                        {
                            l.Add(getBarcode2DImage(l[23].ToString()));// index 23 is boarding pass code.
                        }
                        else
                            l.Add(o);
                        j++;
                    }
                    bp.Tables[0].Rows[0].ItemArray = l.ToArray();
                    bp.Tables[0].Columns.Add(rr.m_printerNameBP);// add printer name......... numpang
                    datareslt.Add(bp);
                } return datareslt;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetWhiteCardRequestReportByMACAddress(string macAddress)
        {
            IList result = new ArrayList();
            IList datareslt = new ArrayList();
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand("select * from report_request r where r.m_macAddress=?m_macAddress and r.report_type = 'WC' and r.status = 'Open'", m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = macAddress;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    ReportRequest rrs = new ReportRequest(rd);
                    result.Add(rrs);
                }
                rd.Close();
                foreach (ReportRequest rr in result)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = "update report_request set status = 'Close' where id=?id";
                    cmd.Parameters.Add("id", MySqlDbType.Int32).Value = rr.id;
                    cmd.ExecuteNonQuery();

                    DataSet bp = m_checkInService.ReportItineraryBookingPassengerByID(rr.report_id);
                    bp.Tables[0].Columns.Add(rr.m_printerNameWC);
                    datareslt.Add(bp);
                }
                return datareslt;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }

        public IList GetKIMSCardRequestReportByMACAddress(string macAddress)
        {
            IList result = new ArrayList();
            IList datareslt = new ArrayList();
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            try
            {
                MySqlCommand cmd = new MySqlCommand("select * from report_request r where r.m_macAddress=?m_macAddress and r.report_type = 'KIMS' and r.status = 'Open'", m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = macAddress;
                MySqlDataReader rd = cmd.ExecuteReader();
                while (rd.Read())
                {
                    ReportRequest rrs = new ReportRequest(rd);
                    result.Add(rrs);
                }
                rd.Close();
                foreach (ReportRequest rr in result)
                {
                    cmd.Parameters.Clear();
                    cmd.CommandText = "update report_request set status = 'Close' where id=?id";
                    cmd.Parameters.Add("id", MySqlDbType.Int32).Value = rr.id;
                    cmd.ExecuteNonQuery();

                    DataSet bp = m_checkInService.ReportKIMSCardPassengerByID(rr.report_id);
                    bp.Tables[0].Columns.Add(rr.m_printerNameKIMS);
                    datareslt.Add(bp);
                }
                return datareslt;
            }
            catch (Exception x)
            {
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }

        }

        public void AddReportRequest(string ip_address, string reportType, long report_id, string macAddress, string BPPrinterName, string WCPrinterName, string KIMSPrinterName)
        {
            m_dataBaseConectivity.InterfaceSqlConnection.Open();
            MySqlCommand cmd = new MySqlCommand(@"INSERT INTO report_request
           (report_type
            ,report_id
            ,status
            ,ip_address
            ,m_macAddress
            ,m_printerNameBP
            ,m_printerNameWC
            ,m_printerNameKIMS
            )
            VALUES
           (?report_type
           ,?report_id
            ,?status
            ,?ip_address
            ,?m_macAddress
            ,?m_printerNameBP
            ,?m_printerNameWC
            ,?m_printerNameKIMS
            )"
                , m_dataBaseConectivity.InterfaceSqlConnection);
            cmd.Parameters.Add("report_type", MySqlDbType.VarChar).Value = reportType;
            cmd.Parameters.Add("report_id", MySqlDbType.Int32).Value = report_id;
            cmd.Parameters.Add("status", MySqlDbType.VarChar).Value = "Open";
            cmd.Parameters.Add("ip_address", MySqlDbType.VarChar).Value = ip_address;
            cmd.Parameters.Add("m_macAddress", MySqlDbType.VarChar).Value = macAddress;
            cmd.Parameters.Add("m_printerNameBP", MySqlDbType.VarChar).Value = BPPrinterName;
            cmd.Parameters.Add("m_printerNameWC", MySqlDbType.VarChar).Value = WCPrinterName;
            cmd.Parameters.Add("m_printerNameKIMS", MySqlDbType.VarChar).Value = KIMSPrinterName;
            cmd.ExecuteNonQuery();
            m_dataBaseConectivity.InterfaceSqlConnection.Close();
        }

        public byte[] getBarcode2DImage(string text)
        {
            QRCodeEncoder qrCodeEncoder = new QRCodeEncoder();
            qrCodeEncoder.QRCodeEncodeMode = QRCodeEncoder.ENCODE_MODE.BYTE;
            qrCodeEncoder.QRCodeScale = 6;
            qrCodeEncoder.QRCodeVersion = 3;
            qrCodeEncoder.QRCodeErrorCorrect = QRCodeEncoder.ERROR_CORRECTION.M;
            Image image = qrCodeEncoder.Encode(text);
            MemoryStream ms = new MemoryStream();
            image.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
            return ms.ToArray();
        }
    }
}
